from typing import List, Dict, Any

import aiomysql
import settings
import pymysql
from decorators import cache_results
from fastapi import APIRouter, Query
from fastapi import exceptions
from fastapi import status
from fastapi.requests import Request
from log import logger

router = APIRouter()


@router.get('/categories/', name='book:categories')
# @cache_results('categories')
async def get_categories(request: Request) -> List[Dict[str, str]]:
    """
    :param request: Request 请求对象
    :return: list 所有的分类
    """
    try:
        async with request.app.state.pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                await cur.execute('SELECT name FROM category ORDER BY rating DESC;')
                return await cur.fetchall()
    except (pymysql.err.OperationalError, ConnectionRefusedError) as e:
        logger.error(str(e))
        raise exceptions.HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=settings.SERVER_ERROR_MESSAGE)
    except Exception as e:  # 不知道啥错误！！
        logger.error(str(e))


@router.get('/books/', name='book:books')  # 这里不要加缓存，会出现重复加载问题
async def get_books(request: Request, limit: int = settings.PER_PAGE, offset: int = 0) -> List[Dict[str, Any]]:
    """
    :param request: Request
    :param limit: int  数据条目
    :param offset: int  偏移量
    :return: list 获取所有书籍- 主页
    """
    try:
        async with request.app.state.pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                columns = ('title', 'country', 'publisher', 'description', 'author', 'pub_year',
                           'cover_path', 'pdf_path', 'num_views')
                rows_str = ', '.join(columns)
                sql = f'SELECT {rows_str} FROM book LIMIT {limit} OFFSET {offset};'
                await cur.execute(sql)
                return await cur.fetchall()
    except pymysql.err.OperationalError:
        raise exceptions.HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=settings.SERVER_ERROR_MESSAGE)
    except Exception as e:
        logger.error(str(e))


@router.get('/books/{title}/', name='book:book')
async def get_book(request: Request, title: str) -> Dict[str, Any]:
    """
    :param request: Request
    :param title: str 书籍标题（唯一)
    :return: dict 获取单个书籍
    """
    try:
        async with request.app.state.pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:

                # 判断该标题的书是否存在
                await cur.execute('select exists (select id from book where title = (%s)) as e;', (title,))
                r = await cur.fetchone()
                if r['e'] == 0:
                    # 抛异常有结果，不需要捕获
                    # {"detail": "您访问的页面不存在!"}
                    raise exceptions.HTTPException(
                        status_code=status.HTTP_404_NOT_FOUND, detail=f'书名: "{title}"不存在')

                # 开启事务
                await conn.begin()
                try:
                    await cur.execute('UPDATE book SET num_views = num_views + 1 WHERE title = (%s);', (title,))
                except Exception:
                    await conn.rollback()
                else:
                    await conn.commit()

                await cur.execute('SELECT pdf_path FROM book WHERE title = (%s);', (title,))
                return await cur.fetchone()
    except pymysql.err.OperationalError as e:
        logger.error(str(e))
        raise exceptions.HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=settings.SERVER_ERROR_MESSAGE)


@router.get('/{category}/books/', name='book:category_books')
async def get_category_books(
        request: Request, category: str, limit: int = settings.PER_PAGE, offset: int = 0) -> List[Dict[str, Any]]:
    """
    :param request:  Request
    :param category: str
    :param limit:  int
    :param offset: int
    :return: 某个分类下的所有图书
    """
    try:
        async with request.app.state.pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                # 需要判断改分类是否存在
                await cur.execute('select exists (select id from category where name = (%s)) as e;', (category,))
                r = await cur.fetchone()
                if r['e'] == 0:
                    raise exceptions.HTTPException(
                        status_code=status.HTTP_404_NOT_FOUND, detail=f'栏目： "{category}"不存在')

                columns = (
                    'title', 'country', 'publisher', 'description',
                    'author', 'pub_year', 'cover_path', 'pdf_path', 'num_views')
                columns_str = ', '.join(columns)

                # 因为book和category表中没有重复名字的列，不使用别名??
                sql = f'''
SELECT {columns_str}, name FROM book INNER JOIN category
ON book.category_id = category.id WHERE category.name = (%s) limit {limit} offset {offset};'''

                await cur.execute(sql, (category,))
                return await cur.fetchall()
    except pymysql.err.OperationalError as e:
        logger.error(str(e))
        raise exceptions.HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=settings.SERVER_ERROR_MESSAGE)


@router.get('/links/')
@cache_results('links')
async def get_links(request: Request) -> List[Dict[str, str]]:
    """
    :param request: Request请求对象
    :return: list 其他网站的有用连接
    """
    try:
        async with request.app.state.pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                await cur.execute('SELECT name, url FROM link ORDER BY rating DESC;')
                return await cur.fetchall()
    except pymysql.err.OperationalError:
        raise exceptions.HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=settings.SERVER_ERROR_MESSAGE)
    except Exception as e:
        logger.error(str(e))


@router.get('/most_recent/')
@cache_results('most_recent')
async def get_most_recent(request: Request) -> List[Dict[str, Any]]:
    """
    :param request: Request请求对象
    :return: list 最近(30天以内)入库的书籍列表，默认显示10条记录
    """
    try:
        async with request.app.state.pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                if settings.DEBUG:
                    columns = (
                        'title', 'country', 'publisher', 'description', 'author', 'pub_year',
                        'cover_path', 'pdf_path', 'num_views', 'creation_time')  # 这里添加creation_time单元测试用前端不会显示
                else:
                    columns = (
                        'title', 'country', 'publisher', 'description', 'author', 'pub_year',
                        'cover_path', 'pdf_path', 'num_views')
                columns_str = ', '.join(columns)
                sql = f'''
SELECT {columns_str} FROM book WHERE creation_time > date_sub(curdate(), INTERVAL 30 DAY) 
ORDER BY creation_time DESC LIMIT {settings.NUM_SHOW_ITEMS};'''
                await cur.execute(sql)
                return await cur.fetchall()
    except pymysql.err.OperationalError as e:
        logger.error(str(e))
        raise exceptions.HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=settings.SERVER_ERROR_MESSAGE)
    except Exception as e:
        logger.error(str(e))


@router.get('/month_most_viewed/')
@cache_results('month_most_viewed', seconds=48 * 3600)
async def get_month_most_viewed(request: Request) -> List[Dict[str, Any]]:
    """
    :param request: Request请求对象
    :return: list 单月浏览最多的书籍列表,默认显示10条记录
    """
    try:
        async with request.app.state.pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                if settings.DEBUG:
                    columns = (
                        'title', 'country', 'publisher', 'description', 'author', 'pub_year',
                        'cover_path', 'pdf_path', 'num_views', 'creation_time')  # creation_time,前端不会显示
                else:  # 减少查询不需要的字段
                    columns = (
                        'title', 'country', 'publisher', 'description', 'author', 'pub_year', 'cover_path',
                        'pdf_path', 'num_views')

                columns_str = ', '.join(columns)
                sql = f'''
SELECT {columns_str} FROM book WHERE date_format(creation_time, '%Y%m') = date_format(curdate(), '%Y%m') 
ORDER BY num_views DESC LIMIT {settings.NUM_SHOW_ITEMS};'''
                await cur.execute(sql)
                return await cur.fetchall()

    except pymysql.err.OperationalError as e:
        logger.error(str(e))
        raise exceptions.HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=settings.SERVER_ERROR_MESSAGE)
    except Exception as e:
        logger.error(str(e))


@router.get('/most_viewed/')
@cache_results('most_viewed', seconds=2 * 60)  # 2分钟
async def get_most_viewed(request: Request) -> List[Dict[str, Any]]:
    """
    :param request: Request请求对象
    :return: list 浏览最多的书籍列表
    """
    try:
        async with request.app.state.pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:

                columns = (
                    'title', 'country', 'publisher', 'description', 'author', 'pub_year',
                    'cover_path', 'pdf_path', 'num_views')

                columns_str = ', '.join(columns)
                sql = f'SELECT {columns_str} FROM book ORDER BY num_views DESC LIMIT {settings.NUM_SHOW_ITEMS};'
                await cur.execute(sql)
                return await cur.fetchall()

    except pymysql.err.OperationalError:
        raise exceptions.HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=settings.SERVER_ERROR_MESSAGE)
    except Exception as e:
        logger.error(str(e))


@router.get('/search')
async def search(request: Request, keyword: str = Query(..., min_length=1), limit: int = settings.PER_PAGE,
                 offset: int = 0) -> List[Dict[str, Any]]:
    """
    :param request: Request请求对象
    :param keyword: str 查找关键字-只实现按标题查找
    :param limit: int  显示结果条目
    :param offset: int 偏移量
    :return: list 查找到的书籍列表
    """
    #  书的标题含有关键字或者书的分类含有关键字
    try:
        async with request.app.state.pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:

                columns = (
                    'title', 'country', 'publisher', 'description', 'author', 'pub_year',
                    'cover_path', 'pdf_path', 'num_views')
                columns_str = ', '.join(columns)

                # 通配符在匹配字符前面会进行全表扫描，有更好的解决方法麽?
                # 根据书的标题获取结果, 标题包含某个关键子，如: like '%python%'
                sql = f"""
SELECT {columns_str} FROM book WHERE title LIKE concat('%%', (%s), '%%') LIMIT {limit} OFFSET {offset};"""
                await cur.execute(sql, (keyword,))
                return await cur.fetchall()  # 如果没数据返回空的元组，如果有数据返回列表

    except pymysql.err.OperationalError as e:
        logger.error(str(e))
        raise exceptions.HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=settings.SERVER_ERROR_MESSAGE)

    except Exception as e:
        logger.error(str(e))
